import java.sql.*;

public class QueryDB {
	//201521123091
	//http://www.tutorialspoint.com/javaexamples/jdbc_prepared_statement.htm
	public static void main(String[] args) throws Exception {
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
		Statement stmt = con.createStatement();
		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS emp (id int primary key, name char(10), job char(10))");
		stmt.executeUpdate("TRUNCATE TABLE emp");
		
		PreparedStatement updateemp = con.prepareStatement("insert into emp (id, name, job) values(?,?,?)");
		
		updateemp.setInt(1, 23);
		updateemp.setString(2, "Roshan");
		updateemp.setString(3, "BOSS");
		updateemp.executeUpdate();
		
		updateemp.setInt(1, 19);
		updateemp.setString(2, "Karl");
		updateemp.setString(3, "Invoker");
		updateemp.executeUpdate();
		
		updateemp.setInt(1, 17);
		updateemp.setString(2, "NeverMore");
		updateemp.setString(3, "SF");
		updateemp.executeUpdate();
		
		String query = "select * from emp";
		ResultSet rs = stmt.executeQuery(query);
		System.out.printf("%-4s%-10s%-10s\n", "id", "name", "job");

		while (rs.next()) {
			int id = rs.getInt("id");
			String name = rs.getString("name");
			String job = rs.getString("job");
			System.out.printf("%-4d%-10s%-10s\n", id, name, job);
		}
		System.out.println("\n");
		
		updateemp = con.prepareStatement("SELECT * FROM emp where id between ? and ?");
		updateemp.setInt(1, 16);
		updateemp.setInt(2, 20);
		rs = updateemp.executeQuery();
		System.out.printf("%-4s%-10s%-10s\n", "id", "name", "job");
		while (rs.next()) {
			int id = rs.getInt("id");
			String name = rs.getString("name");
			String job = rs.getString("job");
			System.out.printf("%-4d%-10s%-10s\n", id, name, job);
		}
		
		con.close();
	}
}